﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;
using System.Windows.Forms;

namespace week6
{
    class Sqlcmd
    {
        private static string url = "data source=D:\\C#\\上传文件\\第六次课堂作业\\week6\\school.db";
        //查找语句
        public static DataTable Select(string query)
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(url);//连接数据库
                con.Open();

                //" ORDER BY "+flag+"id";
                SQLiteCommand cmd = new SQLiteCommand(query, con);//使用sql查找指令
                DataTable dt = new DataTable();
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                adapter.Fill(dt);
                con.Close();
                return dt;
            }
            catch { MessageBox.Show("数据为空！"); return null; }
            
        }

        //删除
        public static void Delete(string flag,string id)
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(url);
                //Console.WriteLine(id);
                con.Open();
                string query = "DELETE FROM " + flag + " WHERE " + flag + "id=" + id;//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    MessageBox.Show("删除成功！");
                }
                else
                {
                    MessageBox.Show("删除失败！");
                }
                con.Close();
            }
            catch { MessageBox.Show("删除失败！"); }
        }
        //修改
        public static void Change(string flag,string id,string changetext)
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(url);
                //Console.WriteLine(id);
                con.Open();
                string query = "UPDATE " + flag + " SET "+changetext+" WHERE " + flag + "id=" + id;//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    MessageBox.Show("修改成功！");
                }
                else
                {
                    MessageBox.Show("修改失败！");
                }
                con.Close();
            }
            catch { MessageBox.Show("修改失败！"); }
        }

        //添加
        public static void Add(string flag,string addtext)
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(url);
                con.Open();
                SQLiteCommand cmd = new SQLiteCommand(addtext, con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch(Exception ex) { MessageBox.Show(ex.Message); }
        }
        //操作记录
        public static void Logsadd(string op,string flag)
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(url);
                con.Open();
                string query = "INSERT INTO logs(Operation,Time)VALUES('" + op+" in "+flag+"','"+DateTime.Now.ToString()+"')";//sql语句
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
        }
        //删除记录
        public static void Dellogs()
        {
            try
            {
                SQLiteConnection con = new SQLiteConnection(url);
                con.Open();
                string query = "DELETE FROM logs;update sqlite_sequence set seq=0 where name='logs';";//SQLITE没有truncate语法，用delete后把自增id初始值设置为0
                SQLiteCommand cmd = new SQLiteCommand(query, con);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
